from datetime import datetime
from typing import Union, Optional

from sqlalchemy import update, CursorResult, text, func, insert, delete
from sqlalchemy.ext.asyncio import AsyncSession
from sqlmodel import select, desc, col, asc
from sqlmodel.sql.expression import SelectOfScalar, Select, and_

from src.base.beans import PageResult
from src.base.constants import id_generator
from src.domain.sys_role import SysRole, SysRoleReq, SysRoleResp
from src.domain.sys_role_meun import SysRoleMenu
from src.domain.sys_user_role import SysUserRole


async def get_role_by_id(dbSession: AsyncSession, id: int) -> SysRole:
    result = await dbSession.execute(select(SysRole).where(SysRole.role_id == id).where(SysRole.del_flag == False))
    return result.scalar_one_or_none()


async def get_role_list_by_ids(dbSession: AsyncSession, ids: list[int]) -> list[SysRole]:
    result = await dbSession.execute(select(SysRole).where(col(SysRole.role_id).in_(ids)))
    return list(result.scalars().all())


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysRoleReq):
    stmt = stmt.where(SysRole.del_flag == False)
    if entity:
        if entity.role_id:
            stmt = stmt.where(SysRole.role_id == entity.role_id)
        if entity.role_name:
            stmt = stmt.where(col(SysRole.role_name).like(f"%{entity.role_name}%"))
        if entity.role_code:
            stmt = stmt.where(SysRole.role_code == entity.role_code)
        if entity.data_scope:
            stmt = stmt.where(SysRole.data_scope == entity.data_scope)
        if entity.status is not None:
            stmt = stmt.where(SysRole.status == entity.status)
        if entity.custom_dept_ids and len(entity.custom_dept_ids) > 0:
            search_sql = [f"{id} MEMBER OF(custom_dept_ids_arr)" for id in entity.custom_dept_ids]
            stmt = stmt.where(text(f"( {' OR '.join(search_sql)} )"))
        if entity.custom_user_ids and len(entity.custom_user_ids) > 0:
            search_sql = [f"{id} MEMBER OF(custom_user_ids_arr)" for id in entity.custom_user_ids]
            stmt = stmt.where(text(f"( {' OR '.join(search_sql)} )"))
        if entity.create_time_range:
            start_time = datetime.strptime(entity.create_time_range[0], "%Y-%m-%d").date()
            end_time = datetime.strptime(entity.create_time_range[1], "%Y-%m-%d").date()
            stmt = stmt.where(
                and_(func.date(SysRole.create_time) >= start_time, func.date(SysRole.create_time) <= end_time))
    return stmt


async def get_role_list_by_condition(dbSession: AsyncSession, entity: Optional[SysRoleReq] = None) -> list[SysRoleResp]:
    if entity is None:
        entity = SysRole()
    stmt = select(SysRole)
    stmt = where_handle(stmt, entity)
    stmt = stmt.order_by(desc(SysRole.sort))
    result = await dbSession.execute(stmt)
    return [SysRoleResp(**role.model_dump()) for role in list(result.scalars())]


async def get_role_page_by_condition(dbSession: AsyncSession, entity: Optional[SysRoleReq] = None) -> PageResult[SysRoleResp]:
    if entity is None:
        entity = SysRoleReq()
    list_stmt = select(SysRole)
    list_stmt = where_handle(list_stmt, entity)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysRole.sort))
    list_stmt = list_stmt.limit(entity.pageSize).offset(entity.pageSize * (entity.current - 1))
    result = await dbSession.execute(list_stmt)
    result = list(result.scalars())
    roles = [SysRoleResp(**role.model_dump()) for role in result]

    total_stmt = select(func.count(SysRole.role_id))
    total_stmt = where_handle(total_stmt, entity)
    total_result = await dbSession.execute(total_stmt)
    total = total_result.scalar_one_or_none()
    return PageResult(total=total, data=roles, current=entity.current, pageSize=entity.pageSize)


async def insert_role(dbSession: AsyncSession, entity: SysRoleReq):
    entity.role_id = next(id_generator)
    insert_data = SysRole(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    result = await dbSession.execute(insert(SysRole).values(**insert_data))
    if entity.menu_ids and len(entity.menu_ids) > 0:
        mappings = [SysRoleMenu(role_id=entity.role_id, menu_id=menu_id) for menu_id in entity.menu_ids]
        await delete_role_menu_mapping_by_ids(dbSession, [entity.role_id])
        await insert_role_menu_mapping(dbSession, mappings)
    return int(result.rowcount)


async def update_role(dbSession: AsyncSession, entity: SysRoleReq = None) -> int:
    update_data = SysRole(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    result: CursorResult = await dbSession.execute(
        update(SysRole).values(**update_data).where(col(SysRole.role_id).__eq__(entity.role_id)))
    if entity.menu_ids and len(entity.menu_ids) > 0:
        mappings = [SysRoleMenu(role_id=entity.role_id, menu_id=menu_id) for menu_id in entity.menu_ids]
        await delete_role_menu_mapping_by_ids(dbSession, [entity.role_id])
        await insert_role_menu_mapping(dbSession, mappings)
    return int(result.rowcount)


async def delete_role(dbSession: AsyncSession, id: int = None) -> int:
    # stmt = update(SysRole).values({"del_flag": True}).where(col(SysRole.role_id).__eq__(id))
    stmt = delete(SysRole).where(col(SysRole.role_id).__eq__(id))
    result: CursorResult = await dbSession.execute(stmt)
    await delete_role_menu_mapping_by_ids(dbSession, [id])
    return int(result.rowcount)


async def delete_roles(dbSession: AsyncSession, ids: list[int] = None) -> int:
    # stmt = update(SysRole).values({"del_flag": True}).where(col(SysRole.role_id).in_(ids))
    stmt = delete(SysRole).where(col(SysRole.role_id).in_(ids))
    result = await dbSession.execute(stmt)
    await delete_role_menu_mapping_by_ids(dbSession, ids)
    return int(result.rowcount)


# def get_role_list_by_user_id(dbSession: Session,user_id: int):
#     role_sql = text(
#         "select r.* from sys_role r left join sys_user_role ur on r.role_id = ur.role_id where ur.user_id = :user_id")
#     role_stmt = select(SysRole).from_statement(role_sql)
#     return mysqlClient.exec(role_stmt, params={"user_id": user_id}).scalars().all()

async def get_role_list_by_user_id(dbSession: AsyncSession, user_id: int) -> list[SysRole]:
    stmt = (select(SysRole)
            .select_from(SysRole)
            .outerjoin(SysUserRole, col(SysUserRole.role_id).__eq__(SysRole.role_id))
            .where(SysUserRole.user_id == user_id))
    result = await dbSession.execute(stmt)
    return list(result.scalars().all())

async def get_role_menu_mapping_list_by_ids(dbSession: AsyncSession, ids: list[int] = None) -> list[SysRoleMenu]:
    if ids is None or len(ids) == 0:
        result = await dbSession.execute(select(SysRoleMenu))
        return list(result.scalars().all())
    else:
        result = await dbSession.execute(select(SysRoleMenu).where(col(SysRoleMenu.role_id).in_(ids)))
        return list(result.scalars().all())


async def delete_role_menu_mapping_by_ids(dbSession: AsyncSession, ids: list[int] = None) -> int:
    result = await dbSession.execute(delete(SysRoleMenu).where(col(SysRoleMenu.role_id).in_(ids)))
    return int(result.rowcount)


async def insert_role_menu_mapping(dbSession: AsyncSession, roleMenuMappings: list[SysRoleMenu] = None):
    dbSession.add_all(roleMenuMappings)
